-- Cambio de version a 446.00 porque se solucionaron algunos problemas en 
-- scripts anteriores y mejor no instalar la 445.
-- aprovecho para mejorar el mensaje de un trigger.
--
-- Function: tg_pad_ins_upd_cuit()

-- DROP FUNCTION tg_pad_ins_upd_cuit();

CREATE OR REPLACE FUNCTION tg_pad_ins_upd_cuit()
  RETURNS trigger AS
$BODY$
DECLARE
vRepetidos Int4;
vCod    varchar;
vNombre varchar;
vRubro  varchar;
Controlo varchar;
BEGIN
CONTROLO := 'N';
if (new.cuit is not null) then
if (TG_OP = 'INSERT') then 
CONTROLO := 'S';
else
if (TG_OP = 'UPDATE') then
IF (old.cuit is null) or (new.cuit <> old.cuit) then 
CONTROLO := 'S';
end if;
end if;
end if;
end if;
if (CONTROLO = 'S') and (new.ComparteCuit = 'N') then
select count(*) into vRepetidos from pad where codpadr like (
select cod || case when length(cod) > length(new.codpadr) then '.' else '' end 
from padr where ((new.codpadr = cod) or (new.codpadr LIKE  '%' || cod || '%'))
and controlacuit = 'S' 
order by length(cod)
limit 1
) || '%' and pad.cuit = new.cuit and pad.id <> new.id;
if vRepetidos > 0 then 
if vRepetidos = 1 then 
select pad.cod, pad.nombre, padr.nombre into vCod, vNombre, vRubro from pad,padr where 
pad.codpadr = padr.cod and padr.tipopada = pad.tipopada and
codpadr like (
select cod || case when length(cod) > length(new.codpadr) then '.' else '' end 
from padr where new.codpadr LIKE  '%' || cod || '%'
and controlacuit = 'S' 
order by length(cod) 
limit 1
) || '%' and pad.cuit = new.cuit and pad.id <> new.id;
raise exception 'Ya existe una entidad con ese numero de cuit: % % del rubro %', 
vCod, vNombre, vRubro;  
else
raise exception 'Existen varias entidades con ese numero de cuit, pruebe tildando compartir cuits en la solapa Fiscal, o consulte con su Ingeniero';  
end if;
end if;
end if;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION tg_pad_ins_upd_cuit()
  OWNER TO postgres;


--==================================
SELECT aaVersion('446.00','445.04');
--==================================
